/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package DAL;

import DTO.DTOProvider;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Pham
 */
public class DALProvider {
    ConnectionDB conn;
    
    public DALProvider()
    {
        conn = new ConnectionDB();
    }
    
    //method get all provider
    public ArrayList<DTOProvider> getAllProviders()
    {
        try{
            ArrayList<DTOProvider> list = new ArrayList<>();
            String querySelect = "SELECT * FROM `provider`";
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                list.add(new DTOProvider(rs));
            }
            return list;
        }
        catch(SQLException ex)
        {
            System.err.println(ex.getMessage());
            return null;
        }
    }
    
    //method insert new provider
    public boolean insertProvider(DTOProvider dtoProvider)
    {
        try{
            String queryInsert = "INSERT INTO `provider`(provider_name, provider_phonenumber, provider_email, provider_fax, provider_address) "
                    + "values(?, ?, ?, ?, ?)";
            PreparedStatement pstmt = conn.getConnectionDB().prepareStatement(queryInsert);
            pstmt.setString(1, dtoProvider.getProviderName());
            pstmt.setString(2, dtoProvider.getProviderPhone());
            pstmt.setString(3, dtoProvider.getProviderEmail());
            pstmt.setString(4, dtoProvider.getProviderFax());
            pstmt.setString(5, dtoProvider.getProviderAddress());
            return pstmt.executeUpdate() == 1;
        }
        catch(SQLException ex)
        {
            System.err.printf(ex.getMessage());
            return false;
        }
    }
    
    //method update provider
    public boolean updateProvider(DTOProvider dtoProvider)
    {
        try{
            String queryUpdate = "UPDATE `provider` SET provider_name = ?, provider_phonenumber = ?, provider_email = ?, "
                    + "provider_fax = ?, provider_address = ? WHERE provider_id = ?";
            PreparedStatement pstmt = conn.getConnectionDB().prepareStatement(queryUpdate);
            pstmt.setString(1, dtoProvider.getProviderName());
            pstmt.setString(2, dtoProvider.getProviderPhone());
            pstmt.setString(3, dtoProvider.getProviderEmail());
            pstmt.setString(4, dtoProvider.getProviderFax());
            pstmt.setString(5, dtoProvider.getProviderAddress());
            pstmt.setInt(6, dtoProvider.getProviderId());
            return pstmt.executeUpdate() == 1;
        }
        catch(SQLException ex)
        {
            System.err.print(ex.getMessage());
            return false;
        }
    }
    
    //method get provider by id
    public DTOProvider getProviderByID(int id)
    {
        try{
            DTOProvider dtoProvider = null;
            String querySelect = "SELECT * FROM `provider` WHERE provider_id = " + id;
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                dtoProvider = new DTOProvider(rs);
            }
            return dtoProvider;
        }
        catch(SQLException ex)
        {
            System.err.print(ex.getMessage());
            return null;
        }
    }
    
    //method delete provider
    public boolean deleteProvider(int id)
    {
        try{
            String queryDelete = "DELETE FROM `provider` WHERE provider_id = " + id;
            return conn.exeUpdate(queryDelete);
        }
        catch(Exception ex)
        {
            //System.err.print(ex.getMessage());
            return false;
        }
    }
}
